package com.jiepos.mpos.biz.transaction.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONObject;
import com.jiepos.mpos.common.constant.DBConstant;
import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.constant.SystemConstant;
import com.jiepos.mpos.common.dao.LogTransJYLSDao;
import com.jiepos.mpos.common.entity.App_Log_Trans_JnlsEntity;
import com.jiepos.mpos.common.entity.GpsEntity;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.service.BaseService;
import com.jiepos.mpos.core.util.DateUtil;
import com.jiepos.mpos.core.util.ExcelUtil;
import com.jiepos.mpos.core.util.StringUtils;

/**
 * 
 * 交易流水业务类
 * 
 * @ProjectName: [mpos1]
 * @Package com.jiepos.mpos.biz.transaction.service
 * @author 肖志伟
 * @version 1.0
 * @since 2017年10月9日 下午2:34:31
 * @history（历次修订内容、修订人、修订时间等） @Copyright (c) 2017 江苏杰博实信息技术有限公司
 */
@Service
public class LogTransJYLSService extends BaseService {

	@Autowired
	private LogTransJYLSDao logTransJYLSDao;

	/**
	 * 交易流水列表页查询结果
	 * 
	 * @param request
	 * @param pagination
	 * 
	 * @param jylsEntity
	 * @param loc_dt_start
	 * @param loc_dt_end
	 * @param custom_name
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map getTransactionLogList(HttpServletRequest request, Pagination pagination,
			App_Log_Trans_JnlsEntity jylsEntity, String loc_dt_start, String loc_dt_end, String custom_name) {
		Map paramMap = this.getTransactionLogSql(request, pagination, jylsEntity, loc_dt_start, loc_dt_end, custom_name);
		pagination.queryString = String.valueOf(paramMap.get("sql"));  				
		log.debug("getTransactionLogList queryString: " + pagination.queryString);
		return logTransJYLSDao.getPaginationMap(pagination, "t1", (List)paramMap.get("paramList"));
	}
	/**
	 * 获取交易流水查询sql
	 * @param request
	 * @param pagination
	 * @param jylsEntity
	 * @param loc_dt_start
	 * @param loc_dt_end
	 * @param custom_name
	 * @return
	 */
	public Map getTransactionLogSql(HttpServletRequest request, Pagination pagination,
			App_Log_Trans_JnlsEntity jylsEntity, String loc_dt_start, String loc_dt_end, String custom_name){
		StringBuffer sqlSb = new StringBuffer();
		sqlSb.append(
				"select t1.trans_id as uuid, TO_CHAR(TO_DATE(t1.loc_dt||t1.loc_ts, 'YYYY-MM-DD HH24:MI:SS'), 'yyyy-mm-dd hh24:mi:ss') as loc_dt, ");
		sqlSb.append("t1.order_no, ");
		sqlSb.append("t2.service_no||'-'||t2.service_name as service_name, ");
		sqlSb.append("t3.param_no||'-'||t3.param_name as purpose_name, ");
		sqlSb.append("t1.custom_no, ");
		sqlSb.append("CONCAT(RPAD (SUBSTR (t4.login_id, 1, 3),LENGTH (t4.login_id) - 4,'*'),SUBSTR (t4.login_id,LENGTH (t4.login_id) - 3,4)) ||'-'|| t4.name as custom_name, ");
//		sqlSb.append("t5.ins_cn_nm||'-'||t5.ins_cd as ins_cn_nm, ");
		sqlSb.append("t6.ins_cd||'-'||t6.ins_cn_nm as app_ins_cn_nm, ");//App所属机构
		sqlSb.append("t1.cycle_no, ");
		sqlSb.append("t1.loc_trace_no, ");
		sqlSb.append("t1.snd_trace_no, ");//终端流水号
		sqlSb.append("t1.trf_trace_no, ");//渠道方流水号
		sqlSb.append("t1.card_ins_nm||t1.card_name as card_ins_nm,");
		sqlSb.append("concat(rpad(substr( t1.act_out_no,1,6 ),length(t1.act_out_no)-4,'*') , substr(t1.act_out_no , length(t1.act_out_no)-3,4)) as act_out_no, ");
		sqlSb.append("concat(rpad(substr( t1.act_in_no,1,6 ),length(t1.act_in_no)-4,'*') , substr(t1.act_in_no , length(t1.act_in_no)-3,4)) as act_in_no, ");
		sqlSb.append("(select code_val ||'-'|| code_desc from " + Table.T_APP_CODE_LIST + " where code_tp = '"
				+ DBConstant.Code.CRD_NATURE + "' and code_val = t1.crd_nature ) as crd_type, ");
		sqlSb.append("to_char(t1.trans_at,'fm9999999990.00') as trans_at, ");
		sqlSb.append("to_char(t1.service_minfee,'fm9999999990.00') as service_minfee,");
		sqlSb.append("(case when t1.service_disc is null then '' else to_char(t1.service_disc/100,'fm9999999990.00')||'%' end) as service_disc, ");
		sqlSb.append("to_char(t1.service_disc_maxfee,'fm9999999990.00') as service_disc_maxfee, ");
		sqlSb.append("to_char(t1.service_attachfee,'fm9999999990.00') as service_attachfee, ");//附加手续费
		sqlSb.append("to_char(t1.midfee,'fm9999999990.00') as midfee, ");
		sqlSb.append("to_char(t1.stl_amt,'fm9999999990.00') as stl_amt, ");
		sqlSb.append("t1.rspcode||'-'||t1.rspmsg as rspcode, ");//响应码
		sqlSb.append("(select code_val||'-'||code_desc from " + Table.T_APP_CODE_LIST + " where code_tp = '"
				+ DBConstant.Code.TRS_STAT + "' and code_val = t1.trs_stat) as trs_stat, ");//交易状态
		sqlSb.append("TO_CHAR(TO_DATE(t1.pay_dt||t1.pay_ts, 'YYYY-MM-DD HH24:MI:SS'), 'yyyy-mm-dd hh24:mi:ss') as pay_dt,");//出款时间
		sqlSb.append("(select code_val||'-'||code_desc from " + Table.T_APP_CODE_LIST + " where code_tp = '"
				+ DBConstant.Code.STLST + "' and code_val = t1.stlst) as stlst, "); //清算状态
		sqlSb.append("t1.trf_stl_trace_no, "); //渠道清算流水号
		sqlSb.append("t1.stl_st_desc, "); //清算状态描述		
		sqlSb.append("t1.loc_stl_dt, ");//本地清算日期
		sqlSb.append("(select code_val||'-'||code_desc from " + Table.T_APP_CODE_LIST + " where code_tp = '"
				+ DBConstant.Code.CKSTAT + "' and code_val = t1.ckstat) as ckstat, ");//比对状态	
		sqlSb.append("t1.localaddress as gps ");//GPS定位信息

		//sqlSb.append("(select code_val||'-'||code_desc from " + Table.T_APP_CODE_LIST + " where code_tp = '"
		//		+ DBConstant.Code.STLFLG + "' and code_val = t1.stlflg) as stlflg  "); //清算标志

		sqlSb.append("from ");
		sqlSb.append(Table.T_APP_LOG_TRANS_JNLS + " t1 ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_INF + " t2 ");
		sqlSb.append("on t1.service_uuid = t2.service_uuid ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_PARAM + " t3 ");
		sqlSb.append("on t1.service_param_uuid = t3.service_param_uuid ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_CUSTOM_INF + " t4 ");
		sqlSb.append("on t1.custom_uuid = t4.custom_uuid ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INS_INF + " t5 ");
		sqlSb.append("on t1.ins_uuid = t5.ins_uuid ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INS_INF + " t6 ");
		sqlSb.append("on t4.ins_uuid = t6.ins_uuid ");
		sqlSb.append("where 1=1 ");
		List list = new ArrayList();
		if (!StringUtils.isBlank(loc_dt_start)) {
			Date date = DateUtil.strToDate(loc_dt_start, DateUtil.FORMAT_DATE_1);
			sqlSb.append("and t1.loc_dt >= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{ //无值默认今天
			sqlSb.append("and t1.loc_dt >= ? ");
			list.add(DateUtil.dateToStr(new Date(), "yyyyMMdd"));
		}

		if (!StringUtils.isBlank(loc_dt_end)) {
			Date date = DateUtil.strToDate(loc_dt_end, DateUtil.FORMAT_DATE_1);
			sqlSb.append("and t1.loc_dt <= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{ //无值默认今天
			sqlSb.append("and t1.loc_dt <= ? ");
			list.add(DateUtil.dateToStr(new Date(), "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(jylsEntity.getCustom_no())) {
			sqlSb.append("and t1.custom_no = ? ");
			list.add(jylsEntity.getCustom_no());
		}
		if (!StringUtils.isBlank(custom_name)) {
			sqlSb.append("and t4.name like ? ");
			list.add("%" + custom_name + "%");
		}
		if (!StringUtils.isBlank(jylsEntity.getAct_out_no())) {
			sqlSb.append("and t1.act_out_no = ? ");
			list.add(jylsEntity.getAct_out_no());
		}
		if (!StringUtils.isBlank(jylsEntity.getLoc_trace_no())) {
			sqlSb.append("and t1.loc_trace_no = ? ");
			list.add(jylsEntity.getLoc_trace_no());
		}
		//终端流水号
		if (!StringUtils.isBlank(jylsEntity.getSnd_trace_no())) {
			sqlSb.append("and t1.snd_trace_no = ? ");
			list.add(jylsEntity.getSnd_trace_no());
		}
		//TRF_TRACE_NO 渠道方流水号
		if (!StringUtils.isBlank(jylsEntity.getTrf_trace_no())) {
			sqlSb.append("and t1.trf_trace_no = ? ");
			list.add(jylsEntity.getTrf_trace_no());
		}
		//APP所属机构
		if (!StringUtils.isBlank(jylsEntity.getIns_uuid())) {
			sqlSb.append("and t6.ins_cd = ? ");
			list.add(jylsEntity.getIns_uuid());
		}
		
		if (!StringUtils.isBlank(jylsEntity.getCustom_login_id())) {
			sqlSb.append("and t1.custom_login_id = ? ");
			list.add(jylsEntity.getCustom_login_id());
		}
		if (!StringUtils.isBlank(jylsEntity.getRspcode())) {
			if(!jylsEntity.getRspcode().equals(SystemConstant.RSPCODE_ERROR)){
				sqlSb.append("and t1.rspcode = ? ");
				list.add(jylsEntity.getRspcode());
			}else{
				sqlSb.append("and t1.rspcode != 'XX' and t1.rspcode != '00' ");
			}
			
		}
		if (!StringUtils.isBlank(jylsEntity.getTrs_stat())) {
			sqlSb.append("and t1.trs_stat = ? ");
			list.add(jylsEntity.getTrs_stat());
		}
		if (!StringUtils.isBlank(jylsEntity.getLoc_stl_dt())) {
			Date date = DateUtil.strToDate(jylsEntity.getLoc_stl_dt());
			sqlSb.append("and t1.loc_stl_dt = ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(jylsEntity.getService_uuid())) {
			sqlSb.append("and t1.service_uuid = ? ");
			list.add(jylsEntity.getService_uuid());
		}
		if (!StringUtils.isBlank(jylsEntity.getService_param_uuid())) {
			sqlSb.append("and t1.service_param_uuid = ? ");
			list.add(jylsEntity.getService_param_uuid());
		}
		if (!StringUtils.isBlank(jylsEntity.getTrf_stl_trace_no())) {
			sqlSb.append("and t1.trf_stl_trace_no = ? ");
			list.add(jylsEntity.getTrf_stl_trace_no());
		}	
		
	    String	sql = super.getCustomAuthSql(request, sqlSb.toString(), "t1", null);
		sql = sql + " order by t1.loc_dt desc, t1.loc_ts desc";
		
		Map dataMap = new HashMap();
		dataMap.put("sql", sql);
		dataMap.put("paramList", list);
		return dataMap;

	}

	/**
	 * 下载交易流水数据
	 * 
	 * @param request
	 * @param response
	 * @param pagination
	 * @param fileName
	 * @param jylsEntity
	 * @param loc_dt_start
	 * @param loc_dt_end
	 * @param custom_name
	 * @param ids
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public String executeSaveTransLogFile(HttpServletRequest request, HttpServletResponse response,
			Pagination pagination, String fileName, App_Log_Trans_JnlsEntity jylsEntity, String loc_dt_start,
			String loc_dt_end, String custom_name, String ids) {
		// TODO Auto-generated method stub
		Map paramMap = this.getTransactionLogSql(request, pagination, jylsEntity, loc_dt_start, loc_dt_end, custom_name);
		String sql = String.valueOf(paramMap.get("sql"));
		List paramList = (List)paramMap.get("paramList");
		List<Map> resultList =  logTransJYLSDao.queryForList(sql,paramList);
		List<String> headList = new ArrayList<String>();
		headList.add("本地交易日期");
		headList.add("订单号");
		headList.add("业务名称");
		headList.add("业务参数名称");
		headList.add("会员编号");
		headList.add("会员名称");
		headList.add("APP所属机构");
		headList.add("批次号");
		headList.add("本地流水号");
		headList.add("终端流水号");
		headList.add("渠道方流水号");
		headList.add("发卡行名称");
		headList.add("交易卡号");
		headList.add("收款账号");
		headList.add("卡类型");
		headList.add("交易金额");
		headList.add("最小手续费");
		headList.add("交易扣率");
		headList.add("封顶手续费");
		headList.add("附加手续费");
		headList.add("会员手续费");
		headList.add("清算金额");
		headList.add("交易结果");
		headList.add("交易状态");
		headList.add("出款时间");
		headList.add("清算状态");
		headList.add("渠道清算流水号");
		headList.add("清算状态描述");
//		headList.add("清算标志");
		headList.add("本地清算日期");
		headList.add("比对状态");
		headList.add("GPS定位信息");
		return ExcelUtil.writeExcel(fileName, "交易流水", headList, resultList);
	}

	 
	
	/**
	 * 调单查询列表数据
	 * 
	 * @param request
	 * @param pagination
	 * 
	 * @param jylsEntity
	 * @param loc_dt_start
	 * @param loc_dt_end
	 * @param custom_name
	 * @return
	 */
	public Map getTransactionLogList1(HttpServletRequest request, Pagination pagination,
			App_Log_Trans_JnlsEntity jylsEntity, String loc_dt_start, String loc_dt_end, String custom_name) {
		JSONObject common = getAllTransLogSqlParams(jylsEntity, loc_dt_start, loc_dt_end, custom_name, null);
		pagination.setQueryString(common.getString("sql"));
		pagination = super.getCustomAuthSql(request, pagination, "t1", null);
		pagination.queryString = pagination.queryString + " order by t1.loc_dt desc, t1.loc_ts desc";
		return logTransJYLSDao.getPaginationMap(pagination, "t1", common.getObject("params", List.class));
	}

	/**
	 * 下载调单查询交易流水数据
	 * 
	 * @param request
	 * @param response
	 * @param pagination
	 * @param fileName
	 * @param jylsEntity
	 * @param loc_dt_start
	 * @param loc_dt_end
	 * @param custom_name
	 * @param ids
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public String executeSaveTransLogFile1(HttpServletRequest request, HttpServletResponse response,
			Pagination pagination, String fileName, App_Log_Trans_JnlsEntity jylsEntity, String loc_dt_start,
			String loc_dt_end, String custom_name, String ids) {
		JSONObject common = getAllTransLogSqlParams(jylsEntity, loc_dt_start, loc_dt_end, custom_name, ids);
		String sql = super.getCustomAuthSql(request, common.getString("sql"), "t1", null);
		sql = sql + " order by t1.loc_dt desc, t1.loc_ts desc";
		List<Map> resultList = logTransJYLSDao.queryForList(sql, common.getObject("params", List.class));
		List<String> headList = new ArrayList<String>();
		headList.add("本地交易日期");
		headList.add("订单号");
		headList.add("业务名称");
		headList.add("业务参数名称");
		headList.add("会员编号");
		headList.add("会员名称");
		headList.add("APP所属机构");
		headList.add("批次号");
		headList.add("本地流水号");
		headList.add("终端流水号");
		headList.add("渠道方流水号");
		headList.add("发卡行名称");
		headList.add("交易卡号");
		headList.add("收款账号");
		headList.add("卡类型");
		headList.add("交易金额");
		headList.add("最小手续费");
		headList.add("交易扣率");
		headList.add("封顶手续费");
		headList.add("附加手续费");
		headList.add("会员手续费");
		headList.add("成本手续费");
		headList.add("清算金额");
		headList.add("毛利");
		headList.add("转发渠道号");
		headList.add("渠道商户号");
		headList.add("渠道终端号");
		headList.add("交易结果");
		headList.add("交易状态");
		headList.add("出款日期");
		headList.add("清算状态");
		headList.add("清算状态描述");
//		headList.add("清算标志");
		headList.add("本地清算日期");
		headList.add("比对状态");
		headList.add("GPS定位信息");
		return ExcelUtil.writeExcel(fileName, "交易流水", headList, resultList);
	}

	/**
	 * 根据筛选条件获取数据
	 * 
	 * @param request
	 * @param jylsEntity
	 * @param loc_dt_start
	 * @param loc_dt_end
	 * @param custom_name
	 * @param ids 
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public JSONObject getAllTransLogSqlParams(App_Log_Trans_JnlsEntity jylsEntity,
			String loc_dt_start, String loc_dt_end, String custom_name, String ids) {
		StringBuffer sqlSb = new StringBuffer();
		sqlSb.append("select t1.trans_id, TO_CHAR(TO_DATE(t1.loc_dt||t1.loc_ts, 'YYYY-MM-DD HH24:MI:SS'), 'yyyy-mm-dd hh24:mi:ss') as loc_dt, ");
		sqlSb.append("t1.order_no, ");
		sqlSb.append("t2.service_no||'-'||t2.service_name as service_name, ");
		sqlSb.append("t3.param_no||'-'||t3.param_name as purpose_name, ");
		sqlSb.append("t1.custom_no, ");
		sqlSb.append("CONCAT(RPAD (SUBSTR (t4.login_id, 1, 3),LENGTH (t4.login_id) - 4,'*'),SUBSTR (t4.login_id,LENGTH (t4.login_id) - 3,4)) ||'-'|| t4.name as custom_name, ");
		sqlSb.append("t6.ins_cd||'-'||t6.ins_cn_nm as app_ins_cn_nm, ");//App所属机构
		sqlSb.append("t1.cycle_no, ");
		sqlSb.append("t1.loc_trace_no, "); //本地流水号
		sqlSb.append("t1.snd_trace_no, "); //终端流水号
		sqlSb.append("t1.trf_trace_no, ");//渠道方流水号
		sqlSb.append("t1.card_ins_nm||t1.card_name as card_ins_nm,");
		sqlSb.append("concat(rpad(substr( t1.act_out_no,1,6 ),length(t1.act_out_no)-4,'*') , substr(t1.act_out_no , length(t1.act_out_no)-3,4)) as act_out_no, ");
		sqlSb.append("concat(rpad(substr( t1.act_in_no,1,6 ),length(t1.act_in_no)-4,'*') , substr(t1.act_in_no , length(t1.act_in_no)-3,4)) as act_in_no, ");
		sqlSb.append("(select code_val ||'-'|| code_desc from " + Table.T_APP_CODE_LIST + " where code_tp = '"
				+ DBConstant.Code.CRD_NATURE + "' and code_val = t1.crd_nature ) as crd_type, ");
		sqlSb.append("to_char(t1.trans_at,'fm9999999990.00') as trans_at, ");
		sqlSb.append("to_char(t1.service_minfee,'fm9999999990.00') as service_minfee,");
		sqlSb.append("(case when t1.service_disc is null then '' else to_char(t1.service_disc/100,'fm9999999990.00')||'%' end) as service_disc, ");
		sqlSb.append("to_char(t1.service_disc_maxfee,'fm9999999990.00') as service_disc_maxfee, ");
		sqlSb.append("to_char(t1.service_attachfee,'fm9999999990.00') as service_attachfee, ");//附加手续费
		sqlSb.append("to_char(t1.midfee,'fm9999999990.00') as midfee, ");
		sqlSb.append("to_char(t1.costfee,'fm9999999990.00') as costfee, ");
		sqlSb.append("to_char(t1.stl_amt,'fm9999999990.00') as stl_amt, ");
		sqlSb.append("to_char(t1.midfee - t1.costfee,'fm9999999990.00') as maoli, "); //毛利
		sqlSb.append("t7.channel_no||'-'||t7.chn_nm as trf_channel_id, "); // 转发渠道号
		sqlSb.append("t1.trf_mchnt_cd, ");	 // 渠道商户号
		sqlSb.append("t1.trf_term_id, ");	 // 渠道终端号
		sqlSb.append("t1.rspcode||'-'||t1.rspmsg as rspcode, ");
		sqlSb.append("(select code_val||'-'||code_desc from " + Table.T_APP_CODE_LIST + " where code_tp = '"
				+ DBConstant.Code.TRS_STAT + "' and code_val = t1.trs_stat) as trs_stat, ");
		sqlSb.append("TO_CHAR(TO_DATE(t1.pay_dt||t1.pay_ts, 'YYYY-MM-DD HH24:MI:SS'), 'yyyy-mm-dd hh24:mi:ss') as pay_dt,");
		sqlSb.append("(select code_val||'-'||code_desc from " + Table.T_APP_CODE_LIST + " where code_tp = '"
				+ DBConstant.Code.STLST + "' and code_val = t1.stlst) as stlst, "); //清算状态
		sqlSb.append("t1.stl_st_desc, "); //清算状态描述
//		sqlSb.append("(select code_val||'-'||code_desc from " + Table.T_APP_CODE_LIST + " where code_tp = '"
//				+ DBConstant.Code.STLFLG + "' and code_val = t1.stlflg) as stlflg, "); //清算标识
		sqlSb.append("t1.loc_stl_dt, ");
		sqlSb.append("(select code_val||'-'||code_desc from " + Table.T_APP_CODE_LIST + " where code_tp = '"
				+ DBConstant.Code.CKSTAT + "' and code_val = t1.ckstat) as ckstat, ");
		sqlSb.append("t1.localaddress as gps ");
		sqlSb.append("from ");
		sqlSb.append(Table.T_APP_LOG_TRANS_JNLS + " t1 ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_INF + " t2 ");
		sqlSb.append("on t1.service_uuid = t2.service_uuid ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_PARAM + " t3 ");
		sqlSb.append("on t1.service_param_uuid = t3.service_param_uuid ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_CUSTOM_INF + " t4 ");
		sqlSb.append("on t1.custom_uuid = t4.custom_uuid ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INS_INF + " t5 ");
		sqlSb.append("on t1.ins_uuid = t5.ins_uuid ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INS_INF + " t6 ");
		sqlSb.append("on t4.ins_uuid = t6.ins_uuid ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INF_CHANNEL + " t7 ");
		sqlSb.append("on t1.trf_channel_id = t7.channel_no ");
		sqlSb.append("where 1=1 ");
		List list = new ArrayList();
		if (!StringUtils.isBlank(loc_dt_start)) {
			Date date = DateUtil.strToDate(loc_dt_start, DateUtil.FORMAT_DATE_1);
			sqlSb.append("and t1.loc_dt >= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{ //无值默认今天
			sqlSb.append("and t1.loc_dt >= ? ");
			list.add(DateUtil.dateToStr(new Date(), "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(loc_dt_end)) {
			Date date = DateUtil.strToDate(loc_dt_end, DateUtil.FORMAT_DATE_1);
			sqlSb.append("and t1.loc_dt <= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{ //无值默认今天
			sqlSb.append("and t1.loc_dt <= ? ");
			list.add(DateUtil.dateToStr(new Date(), "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(jylsEntity.getCustom_no())) {
			sqlSb.append("and t1.custom_no = ? ");
			list.add(jylsEntity.getCustom_no());
		}
		if (!StringUtils.isBlank(custom_name)) {
			sqlSb.append("and t4.name like ? ");
			list.add("%" + custom_name + "%");
		}
		if (!StringUtils.isBlank(jylsEntity.getAct_out_no())) {
			sqlSb.append("and t1.act_out_no = ? ");
			list.add(jylsEntity.getAct_out_no());
		}
		if (!StringUtils.isBlank(jylsEntity.getLoc_trace_no())) {
			sqlSb.append("and t1.loc_trace_no = ? ");
			list.add(jylsEntity.getLoc_trace_no());
		}
		//终端流水号
		if (!StringUtils.isBlank(jylsEntity.getSnd_trace_no())) {
			sqlSb.append("and t1.snd_trace_no = ? ");
			list.add(jylsEntity.getSnd_trace_no());
		}
		//TRF_TRACE_NO 渠道方流水号
		if (!StringUtils.isBlank(jylsEntity.getTrf_trace_no())) {
			sqlSb.append("and t1.trf_trace_no = ? ");
			list.add(jylsEntity.getTrf_trace_no());
		}
		if (!StringUtils.isBlank(jylsEntity.getIns_uuid())) {
			sqlSb.append("and t6.ins_cd = ? ");
			list.add(jylsEntity.getIns_uuid());
		}

		if (!StringUtils.isBlank(jylsEntity.getCustom_login_id())) {
			sqlSb.append("and t1.custom_login_id = ? ");
			list.add(jylsEntity.getCustom_login_id());
		}
		if (!StringUtils.isBlank(jylsEntity.getRspcode())) {
			sqlSb.append("and t1.rspcode = ? ");
			list.add(jylsEntity.getRspcode());
		}
		if (!StringUtils.isBlank(jylsEntity.getTrs_stat())) {
			sqlSb.append("and t1.trs_stat = ? ");
			list.add(jylsEntity.getTrs_stat());
		}
		if (!StringUtils.isBlank(jylsEntity.getLoc_stl_dt())) {
			Date date = DateUtil.strToDate(jylsEntity.getLoc_stl_dt());
			sqlSb.append("and t1.loc_stl_dt = ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(jylsEntity.getService_uuid())) {
			sqlSb.append("and t1.service_uuid = ? ");
			list.add(jylsEntity.getService_uuid());
		}
		if (!StringUtils.isBlank(jylsEntity.getService_param_uuid())) {
			sqlSb.append("and t1.service_param_uuid = ? ");
			list.add(jylsEntity.getService_param_uuid());
		}
		//转发渠道号
		if (!StringUtils.isBlank(jylsEntity.getTrf_channel_id())) {
			sqlSb.append("and t1.trf_channel_id = ? ");
			list.add(jylsEntity.getTrf_channel_id());
		}
		//渠道商户号
		if (!StringUtils.isBlank(jylsEntity.getTrf_mchnt_cd())) {
			sqlSb.append("and t1.trf_mchnt_cd = ? ");
			list.add(jylsEntity.getTrf_mchnt_cd());
		}
		//渠道终端号
		if (!StringUtils.isBlank(jylsEntity.getTrf_term_id())) {
			sqlSb.append("and t1.trf_term_id = ? ");
			list.add(jylsEntity.getTrf_term_id());
		}
		if(!StringUtils.isBlank(ids)){
			String[] idArray = StringUtils.split(ids, ",");
			String idsStr = "";
			for(String id : idArray){
				idsStr += "'"+id+"',";
			}
			idsStr = idsStr.substring(0, idsStr.length()-1);
			idsStr += "";
			sqlSb.append("and t1.trans_id IN ("+idsStr+") ");
		}
		JSONObject common = new JSONObject();
		common.put("sql", sqlSb.toString());
		common.put("params", list);
		return common;
	}
	

	/**
	 * 根据登录账户得到受卡方系统跟踪号
	 * 		策略： 取得登录用户当天最大跟踪号 + 1
	 * 			  如果最大为999999 则 返回 000001
	 * @param login_id
	 * @return
	 */
	public String getSndTraceNO(String login_id){
		String sql = "SELECT MAX(SND_TRACE_NO) AS NO FROM "+ Table.T_APP_LOG_TRANS_JNLS+" WHERE CUSTOM_LOGIN_ID = ? AND SND_DT = ?";
		List<String> params = new ArrayList<>();
		params.add(login_id);
		params.add(DateUtil.getCurrDateStr());
		List<Map<String,Object>> list = logTransJYLSDao.queryForList(sql, params);
		int result = 0;
		if(list != null && list.size() > 0 && list.get(0).get("NO") != null){
			//现在的最大跟踪号
			String no = list.get(0).get("NO").toString();
			if(!StringUtils.isBlank(no)){
				int next = Integer.parseInt(no)+1;
				if(next <= 999999){
					result = next;
				}
			}
		}
		result = result == 0 ? 1 : result;
		//格式化输出结果
		return String.format("%06d", result);
	}

	/**
	 * 根据会员编号得到会员当天交易金额
	 * @param custom_no
	 * @return
	 */
	public String getTransATByCustomNo(String custom_no){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" SELECT TO_CHAR(NVL(SUM(TRANS_AT),0.00),'fm9999999990.00') AS TRANS_AT ")
				.append("   FROM  ")
				.append(DBConstant.Table.T_APP_LOG_TRANS_JNLS)
				.append("  WHERE RSPCODE = ? ")
				.append("    AND CUSTOM_NO = ? ")
				.append("    AND LOC_DT = ? ");
		List<String> params = new ArrayList<>();
		params.add(SystemConstant.RSPCODE_SUCCESS); //交易返回码为 "00"
		params.add(custom_no); //会员 编号
		params.add(DateUtil.getCurrDateStr()); //当天
		List<Map<String,Object>> list = logTransJYLSDao.queryForList(sqlBuf.toString(), params);
		if(list!= null && list.size() > 0 && list.get(0) != null && list.get(0).get("TRANS_AT") != null){
			return String.valueOf(list.get(0).get("TRANS_AT"));
		}else{
			return "0.00";
		}
	}

	/**
	 * 根据会员编号得到会员累计交易金额
	 * @param custom_no
	 * @return
	 */
	public String getSumTransATByCustomNo(String custom_no){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" SELECT TO_CHAR(NVL(SUM(TRANS_AT),0.00),'fm9999999990.00') AS TRANS_AT ")
				.append("   FROM  ")
				.append(DBConstant.Table.T_APP_LOG_TRANS_JNLS)
				.append("  WHERE RSPCODE = ? ")
				.append("    AND CUSTOM_NO = ? ");
		List<String> params = new ArrayList<>();
		params.add(SystemConstant.RSPCODE_SUCCESS); //交易返回码为 "00"
		params.add(custom_no); //会员 编号
		List<Map<String,Object>> list = logTransJYLSDao.queryForList(sqlBuf.toString(), params);
		if(list!= null && list.size() > 0 && list.get(0) != null && list.get(0).get("TRANS_AT") != null){
			return String.valueOf(list.get(0).get("TRANS_AT"));
		}else{
			return "0.00";
		}
	}

	/**
	 * 根据会员uuid得到会员当天交易数量
	 * @param custom_uuid
	 * @return
	 */
	public String getTransCountByCustomUUID(String custom_uuid){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" SELECT COUNT(1) COUN")
				.append("   FROM ")
				.append(DBConstant.Table.T_APP_LOG_TRANS_JNLS)
				.append("  WHERE RSPCODE = ? ")
				.append("    AND CUSTOM_UUID = ? ")
				.append("    AND LOC_DT = ? ");
		List<String> params = new ArrayList<>();
		params.add(SystemConstant.RSPCODE_SUCCESS); //交易返回码为 "00"
		params.add(custom_uuid); //会员 UUID
		params.add(DateUtil.getCurrDateStr()); //当天
		List<Map<String,Object>> list = logTransJYLSDao.queryForList(sqlBuf.toString(), params);
		if(list!= null && list.size() > 0 && list.get(0) != null && list.get(0).get("COUN") !=null){
			return String.valueOf(list.get(0).get("COUN"));
		}else{
			return "0";
		}
	}

	/**
	 * 查询交易流水
	 * @param login_id
	 * @param custom_no
	 * @param begin_tm
	 * @param end_tm
	 * @param curr_page
	 * @param rows_perpage
	 * @param gpsEntity
	 * @return
	 */
	public List getLogTrans(HttpServletRequest request,String login_id, String custom_no, String begin_tm, String end_tm,
							String curr_page, String rows_perpage, GpsEntity gpsEntity) {
		Pagination pagination = new  Pagination();
		pagination.setPage(Integer.parseInt(curr_page));
		pagination.setRows(Integer.parseInt(rows_perpage));
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT  T1.order_no as \"order_no\",")
				.append(" TO_CHAR(TO_DATE(t1.loc_dt||t1.loc_ts, 'YYYY-MM-DD HH24:MI:SS'), 'yyyy-mm-dd hh24:mi:ss') as \"trans_tm\",")
				.append(" t2.service_name||'['||t3.param_name ||']' as \"service_name\", ")
				.append(" concat(rpad(substr( t1.act_out_no,1 , 6 ),length( t1.act_out_no )-4,'*') , substr( t1.act_out_no , length( t1.act_out_no)-3 , 4 ) ) as \"act_out_no\", ")
				.append(" to_char(t1.trans_at,'fm9999999990.00') as \"trans_at\",")
				.append(" t1.rspcode || '-'|| rspmsg \"rsp_code\" ,")
				.append(" to_char(t1.midfee,'fm9999999990.00') as \"midfee\",")
				.append(" to_char(NVL(t1.stl_amt,0.00),'fm9999999990.00') as \"stl_amt\",")
				.append(" t1.localaddress as \"localaddress\" ")
				.append(" from "+Table.T_APP_LOG_TRANS_JNLS+" T1 LEFT JOIN  "+Table.T_APP_SERVICE_INF+" T2 ON t1.service_uuid = t2.service_uuid  ")
				.append(" LEFT JOIN  "+Table.T_APP_SERVICE_PARAM+"  t3  ON T1.SERVICE_PARAM_UUID = T3.SERVICE_PARAM_UUID  ")
				.append(" where  t1.custom_no = ? ");
		List list = new ArrayList();
		list.add(custom_no);
		if (!StringUtils.isBlank(begin_tm)) {
			sb.append("and t1.loc_dt >= ? ");
 			list.add(begin_tm);
 		}
		if (!StringUtils.isBlank(end_tm)) {
			sb.append("and t1.loc_dt <= ? ");
			list.add(end_tm);
		}
		sb.append(" order by t1.loc_dt desc, t1.loc_ts desc ");
		pagination.queryString = sb.toString();
		pagination = logTransJYLSDao.getPagination(pagination, "t1", list);
		List<Map<String,String>> resultList =  pagination.getOldData();
		return resultList;
	}
	
	/**
	 * 根据本地流水号查询流水记录
	 * @param transNo 流水号
	 * @return
	 */
	public Map<String, Object> getTransByLocTransNo(String transNo){
		StringBuffer sql = getSqlForMonitor();
		sql.append(" WHERE T.LOC_TRACE_NO = ?");
		List<Map<String, Object>> list = logTransJYLSDao.queryForListMap(sql.toString(), new Object[]{transNo});
		return list != null && !list.isEmpty() ? list.get(0) : null;
	}
	
	/**
	 * 查询最后十条交易流水
	 * @return
	 */
	public List<Map<String, Object>> getLastTenTrans(){
		StringBuffer sql = new StringBuffer("SELECT * FROM (");
		sql.append(getSqlForMonitor());
		sql.append(" ORDER BY T.LOC_DT || T.LOC_TS DESC) WHERE ROWNUM <= ?");
		return logTransJYLSDao.queryForListMap(sql.toString(), new Object[]{10});
	}
	
	private StringBuffer getSqlForMonitor(){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT TO_CHAR(TO_DATE(T.LOC_DT || T.LOC_TS, 'YYYYMMDDHH24MISS'),")
		.append("               'YYYY-MM-DD HH24:MI:SS') TRANS_TIME,")
		.append("       NVL(T.CARD_INS_NM,' ') CARD_INS_NM,")
		.append("       SI.SERVICE_NAME,")
		.append("       SP.PARAM_NAME,")
		.append("       T.CUSTOM_NO || '(' || LPAD(SUBSTR(CI.NAME, LENGTH(CI.NAME), 1), LENGTH(CI.NAME) + 1, '*') || ')' CUSTOM_NAME,")
		.append("       NVL(RPAD(SUBSTR(T.ACT_OUT_NO, 1, 6), LENGTH(T.ACT_OUT_NO) - 4, '*') || SUBSTR(T.ACT_OUT_NO, LENGTH(T.ACT_OUT_NO) - 3, 4), ' ') ACCT_NO,")
		.append("       TO_CHAR(T.TRANS_AT, 'FM999999999999990.00') TRANS_AT,")
		.append("       T.RSPCODE || '-' || T.RSPMSG RSP_RESULT,")
		.append("       T.LOC_TRACE_NO")
		.append("  FROM ").append(Table.T_APP_LOG_TRANS_JNLS).append(" T")
		.append("  LEFT JOIN ").append(Table.T_APP_CUSTOM_INF).append(" CI")
		.append("    ON T.CUSTOM_UUID = CI.CUSTOM_UUID")
		.append("  LEFT JOIN ").append(Table.T_APP_SERVICE_INF).append(" SI")
		.append("    ON T.SERVICE_UUID = SI.SERVICE_UUID")
		.append("  LEFT JOIN ").append(Table.T_APP_SERVICE_PARAM).append(" SP")
		.append("    ON T.SERVICE_PARAM_UUID = SP.SERVICE_PARAM_UUID");
		return sql;
	}
}
